package edu.hrbeu.crc.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import edu.hrbeu.crc.common.utils.ExcelUtil;
import edu.hrbeu.crc.constant.BillStatus;
import edu.hrbeu.crc.constant.ErrorConstant;
import edu.hrbeu.crc.context.BaseContext;
import edu.hrbeu.crc.mapper.BillMapper;
import edu.hrbeu.crc.pojo.dto.*;
import edu.hrbeu.crc.pojo.entity.Bill;
import edu.hrbeu.crc.pojo.entity.Category;
import edu.hrbeu.crc.pojo.query.BillChatQuery;
import edu.hrbeu.crc.pojo.query.BillQuery;
import edu.hrbeu.crc.pojo.result.PageResult;
import edu.hrbeu.crc.pojo.vo.*;
import edu.hrbeu.crc.service.BillService;
import edu.hrbeu.crc.service.CategoryService;
import jakarta.annotation.Resource;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

@Service
public class BillServiceImpl extends ServiceImpl<BillMapper, Bill> implements BillService {

    @Resource
    private BillMapper billMapper;
    @Resource
    private CategoryService categoryService;

    /**
     * 添加账单
     * @param billDTO
     */
    @Override
    public boolean add(BillDTO billDTO) {
        Bill bill = new Bill();
        BeanUtils.copyProperties(billDTO,bill);
        // 用户id、创建时间
        bill.setUserId(BaseContext.getCurrentId());
        bill.setCreateTime(LocalDateTime.now());
        // 查询类别id
        if (billDTO.getCategoryId() == null) {
            Category c = categoryService.getByName(billDTO.getCategoryName());
            bill.setCategoryId(c.getId());
        }else {
            bill.setCategoryId(billDTO.getCategoryId());
        }
        return this.save(bill);
    }

    /**
     * 分页查询
     * @param billQuery
     * @return
     */
    @Override
    public PageResult<BillPageVO> queryBillByPage(BillQuery billQuery) {

        billQuery.setUserId(BaseContext.getCurrentId());

        Page<BillPageVO> page = billQuery.toPage("b.date", false);

        // 1. 创建 QueryWrapper 对象
        QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
        // 2. 添加基本查询条件
        queryWrapper.eq(billQuery.getType() != null, "b.type", billQuery.getType())
                .eq(billQuery.getUserId() != null, "b.user_id", billQuery.getUserId())
                .eq(billQuery.getCategoryId() != null, "b.category_id", billQuery.getCategoryId())
                .ge(billQuery.getStartDate() != null, "b.date", billQuery.getStartDate())
                .le(billQuery.getEndDate() != null, "b.date", billQuery.getEndDate())
                .ge(billQuery.getStartAmount() != null, "b.amount", billQuery.getStartAmount())
                .le(billQuery.getEndAmount() != null, "b.amount", billQuery.getEndAmount());

        // 3. 添加联表查询条件
        if (StringUtils.isNotBlank(billQuery.getCategoryName())) {
            queryWrapper.apply("c.name = {0}", billQuery.getCategoryName());
        }
        Page<BillPageVO> billVOPage = billMapper.selectBillVOPage(page, queryWrapper);
        return PageResult.<BillPageVO>builder()
                .total(billVOPage.getTotal())
                .page(billVOPage.getCurrent())
                .list(billVOPage.getRecords())
                .build();
    }

    /**
     * 根据id查询
     * @param id
     * @return
     */
    @Override
    public BillVO getBill(Integer id) {
        Bill bill = this.getById(id);
        BillVO billVO = new BillVO();
        BeanUtils.copyProperties(bill, billVO);
        // 根据分类id查询分类名
        Category category = categoryService.getById(bill.getCategoryId());
        billVO.setCategoryName(category.getName());
        billVO.setCategoryLogo(category.getLogo());
        return billVO;
    }

    /**
     * 修改账单
     * @param billDTO
     */
    @Override
    public void updateBill(BillDTO billDTO) {
        Bill bill = new Bill();
        BeanUtils.copyProperties(billDTO,bill);
        // 设置用户id、类别id
        bill.setUserId(BaseContext.getCurrentId());
        if (billDTO.getCategoryId() == null && billDTO.getCategoryName() != null) {
            Category category = categoryService.getByName(billDTO.getCategoryName());
            bill.setCategoryId(category.getId());
        }

        UpdateWrapper<Bill> updateWrapper = new UpdateWrapper<>();
        updateWrapper.lambda()
                        .set(bill.getAmount() != null, Bill::getAmount, bill.getAmount())
                        .set(bill.getDate() != null, Bill::getDate, bill.getDate())
                        .set(bill.getCategoryId() != null, Bill::getCategoryId, bill.getCategoryId())
                        .set(bill.getRemark() != null, Bill::getRemark, bill.getRemark())
                        .eq(Bill::getId, bill.getId());
        this.update(updateWrapper);
    }

    /**
     * 批量删除
     * @param ids
     */
    @Transactional
    @Override
    public void delete(List<Integer> ids) {
        this.removeByIds(ids);
    }

    /**
     * 获取总金额
     * @param userId
     * @param type
     * @return
     */
    private BigDecimal getSumAmount(Integer userId, Integer type) {
        QueryWrapper<Bill> wrapper = new QueryWrapper<>();
        wrapper.lambda()
                .eq(Bill::getUserId, userId)
                .eq(Bill::getType, type);

        // 使用MyBatisPlus的selectObjs方法统计总金额
        List<Object> amountList = baseMapper.selectObjs(wrapper.select("SUM(amount)"));
        if (amountList.get(0) == null) {
            return BigDecimal.ZERO;  // 无支出时返回0
        }
        return new BigDecimal(amountList.get(0).toString());
    }


    /**
     * 获取消费占比图
     * @param consumptionDTO
     * @return
     */
    @Override
    public ConsumptionVO getConsumptionProportion(ConsumptionDTO consumptionDTO) {
        Integer userId = BaseContext.getCurrentId();

        // 空账单
        BigDecimal sum = this.getSumAmount(userId, consumptionDTO.getType());
        if (sum.compareTo(BigDecimal.ZERO) == 0) {
            return new ConsumptionVO();
        }

        List<CategoryAmountDTO> categoryAmountList = billMapper.selectCategoryAmount(userId,
                                                                        consumptionDTO);
        /*if (CollectionUtils.isEmpty(categoryAmountList)) {
            return new ConsumptionVO();
        }*/

        List<String> categoryNames = new ArrayList<>();
        List<Double> amount = new ArrayList<>();
        List<Object> ratios = new ArrayList<>();

        for (CategoryAmountDTO dto : categoryAmountList) {
            categoryNames.add(dto.getCategoryName());
            amount.add(dto.getAmount().doubleValue());
            BigDecimal ratio = dto.getAmount().divide(sum, 4, RoundingMode.HALF_UP)  // 先保留4位小数避免精度丢失
                    .multiply(new BigDecimal(100))
                    .setScale(2, RoundingMode.HALF_UP);  // 最终保留2位小数
            ratios.add(ratio);
        }

        return new ConsumptionVO(categoryNames,amount,ratios);
    }

    /**
     * 获取月度趋势图
     * @param monthConsumptionDTO
     * @return
     */
    @Override
    public MonthlyTrendVO getMonthlyConsumptionTrend(MonthConsumptionDTO monthConsumptionDTO) {
        Integer userId = BaseContext.getCurrentId();

        if (monthConsumptionDTO.getYear() == null) {
            monthConsumptionDTO.setYear(LocalDate.now().getYear());
        }

        List<MonthAmountDTO> dtos = billMapper.selectMonthAmount(userId,monthConsumptionDTO);

        List<String> months = new ArrayList<>();
        List<Double> amounts = new ArrayList<>();

        for (int i = 1; i <= 12; i++) {
            months.add(String.format("%d-%02d", monthConsumptionDTO.getYear(),i));
        }

        // 对没有查到的月份填充zero
        Map<String,BigDecimal> map = new HashMap<>();
        for (MonthAmountDTO dto : dtos) {
            map.put(dto.getMonth(), dto.getAmount());
        }
        for (String m : months) {
            BigDecimal amount = map.getOrDefault(m, BigDecimal.ZERO);
            amounts.add(amount.setScale(2,RoundingMode.HALF_UP).doubleValue());
        }

        return new MonthlyTrendVO(months,amounts);
    }

    /**
     * 导出账单
     * @param consumptionDTO
     * @param response
     */
    @Override
    public void getReport(ConsumptionDTO consumptionDTO, HttpServletResponse response) {
        // 1.获取数据
        Integer userId = BaseContext.getCurrentId();
        // 获取总支出、总收入
        BigDecimal spendingAmount = this.getSumAmount(userId, BillStatus.EXPENSE);//0
        BigDecimal incomeAmount = this.getSumAmount(userId, BillStatus.INCOME);//1

        // 获取TOP占比
        consumptionDTO.setType(BillStatus.EXPENSE);
        List<CategoryAmountDTO> spendingList = billMapper.selectCategoryAmount(userId, consumptionDTO);
        consumptionDTO.setType(BillStatus.INCOME);
        List<CategoryAmountDTO> incomeList = billMapper.selectCategoryAmount(userId, consumptionDTO);

        List<BillVO> vos = billMapper.selectByUserId(userId, consumptionDTO.getStartDate(), consumptionDTO.getEndDate());

        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/model.xlsx");
        // 2.写入
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFCellStyle style = ExcelUtil.createCommonCellStyle(workbook);
            XSSFSheet sheet1 = workbook.getSheet("sheet1");
            // 时间
            XSSFRow secondRow = ExcelUtil.getOrCreateRow(sheet1, 1);
            LocalDate startDate = consumptionDTO.getStartDate();
            LocalDate endDate = consumptionDTO.getEndDate();
            if (startDate != null && vos != null && !vos.isEmpty()) {
                ExcelUtil.getOrCreateCell(secondRow, 1,style).setCellValue(String.valueOf(startDate));
            }else if (vos == null || vos.isEmpty()){
                ExcelUtil.getOrCreateCell(secondRow, 1,style).setCellValue("暂无账单");
            }else {
                // 账单最早的日期
                ExcelUtil.getOrCreateCell(secondRow, 1,style).setCellValue(String.valueOf(vos.get(vos.size() - 1).getDate()));
            }
            if (endDate != null && vos != null && !vos.isEmpty()) {
                ExcelUtil.getOrCreateCell(secondRow, 3,style).setCellValue(String.valueOf(endDate));
            }else if (vos == null || vos.isEmpty()){
                ExcelUtil.getOrCreateCell(secondRow, 3,style).setCellValue("暂无账单");
            }else {
                // 账单最晚日期
                ExcelUtil.getOrCreateCell(secondRow, 3,style).setCellValue(String.valueOf(vos.get(0).getDate()));
            }
            // 总收入和总支出
            XSSFRow thirdRow = sheet1.getRow(2);
            ExcelUtil.getOrCreateCell(thirdRow, 1,style).setCellValue(incomeAmount.setScale(2,RoundingMode.HALF_UP).doubleValue());
            ExcelUtil.getOrCreateCell(thirdRow, 3,style).setCellValue(spendingAmount.setScale(2,RoundingMode.HALF_UP).doubleValue());

            // 支出占收入比例、储蓄率
            // 总支出占总收入比例：总支出/总收入 * 100%
            // 储蓄率：（总收入 - 总支出） / 总收入 * 100%
            XSSFRow forthRow = ExcelUtil.getOrCreateRow(sheet1, 3);
            BigDecimal firstRadio = null;
            if (incomeAmount.signum() == 0) {
                firstRadio = BigDecimal.ZERO;
            } else {
                firstRadio = spendingAmount.divide(incomeAmount, 4, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100))
                        .setScale(2, RoundingMode.HALF_UP);
            }
            ExcelUtil.getOrCreateCell(forthRow, 1,style).setCellValue(firstRadio.doubleValue() + "%");
            BigDecimal secondRadio = null;
            if (incomeAmount.signum() == 0) {
                secondRadio = BigDecimal.ZERO;
            } else {
                secondRadio = incomeAmount.subtract(spendingAmount)
                        .divide(incomeAmount, 4, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100))
                        .setScale(2, RoundingMode.HALF_UP);
            }

            ExcelUtil.getOrCreateCell(forthRow, 3,style).setCellValue(secondRadio.doubleValue() + "%");

            // TOP
            for (int i = 4; i < 10; i++) {
                XSSFRow row = ExcelUtil.getOrCreateRow(sheet1, i);
                CategoryAmountDTO spendingDTO = spendingList.get(i - 4);
                CategoryAmountDTO incomeDTO = incomeList.get(i - 4);
                ExcelUtil.getOrCreateCell(row, 1,style).setCellValue(spendingDTO.getCategoryName());
                BigDecimal spendingRadio = spendingDTO.getAmount().divide(spendingAmount, 4, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100))
                        .setScale(2, RoundingMode.HALF_UP);
                ExcelUtil.getOrCreateCell(row, 2,style).setCellValue(spendingRadio.doubleValue() + "%");

                ExcelUtil.getOrCreateCell(row, 4,style).setCellValue(incomeDTO.getCategoryName());
                BigDecimal incomeRadio = incomeDTO.getAmount().divide(spendingAmount, 4, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100))
                        .setScale(2, RoundingMode.HALF_UP);
                ExcelUtil.getOrCreateCell(row, 5,style).setCellValue(incomeRadio.doubleValue() + "%");
            }

            // 账单
            int count = 2;
            XSSFSheet sheet2 = workbook.getSheet("sheet2");
            for (BillVO vo : vos) {
                XSSFRow row = ExcelUtil.getOrCreateRow(sheet2, count);

                ExcelUtil.getOrCreateCell(row, 0,style).setCellValue(count - 1);
                ExcelUtil.getOrCreateCell(row, 1,style).setCellValue(vo.getType() == 0? "支出":"收入");
                ExcelUtil.getOrCreateCell(row, 2,style).setCellValue(String.valueOf(vo.getDate()));
                ExcelUtil.getOrCreateCell(row, 3,style).setCellValue(vo.getAmount());
                ExcelUtil.getOrCreateCell(row, 4,style).setCellValue(vo.getCategoryName());
                ExcelUtil.getOrCreateCell(row, 5,style).setCellValue(vo.getRemark());
                count++;
            }


            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            String fileName = "账单_" + LocalDate.now() + ".xlsx";
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));

            ServletOutputStream outputStream = response.getOutputStream();

            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(ErrorConstant.FAILED_EXPORT_BILL, e);// 账单导出失败
        }
    }

    /**
     * 导入账单
     * @param file
     */
    @Override
    @Transactional
    public void importBill(MultipartFile file) {
        Integer userId = BaseContext.getCurrentId();
        Set<String> headers = new HashSet<>(Arrays.asList("类型","金额","日期","分类名称","描述"));
        try {
            InputStream inputStream = file.getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            // 1.找到表头
            XSSFSheet sheet = workbook.getSheet("sheet2");
            int headerRowNum = 1;
            XSSFRow headerRow = null;
            try {
                headerRow = sheet.getRow(headerRowNum);
            } catch (NullPointerException e) {
                throw new NullPointerException(ErrorConstant.SHEET2_NOT_EXIST);
            }
            if (headerRow == null) {
                throw new IllegalArgumentException(ErrorConstant.TABLE_HEADER_NOT_EXIST);// 表头行不存在，请检查文件格式
            }

            // 表头行索引
            Map<String, Integer> headerMap = new HashMap<>();
            for (int i = headerRow.getFirstCellNum(); i < headerRow.getLastCellNum(); i++) {
                XSSFCell cell = headerRow.getCell(i);
                if (cell == null) continue;

                String headerName = cell.getStringCellValue().trim();
                if (headers.contains(headerName)) {
                    headerMap.put(headerName, i);
                }
            }

            if (headerMap.size() != headers.size()) {
                Set<String> missingHeaders = new HashSet<>(headers);
                missingHeaders.removeAll(headerMap.keySet());
                throw new IllegalArgumentException(ErrorConstant.MISSING_TABLE_HEADER + missingHeaders);// 表头缺失
            }

            int lastRowNum = sheet.getLastRowNum();
            List<Bill> list = new ArrayList<>();

            for (int i = headerRowNum + 1; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) continue;

                BillDTO dto = ExcelUtil.parseBillFromRow(row, headerMap);
                Bill bill = new Bill();
                BeanUtils.copyProperties(dto, bill);
                Category category = categoryService.getByName(dto.getCategoryName());
                bill.setUserId(userId);
                bill.setCategoryId(category.getId());
                bill.setCreateTime(LocalDateTime.now());

                list.add(bill);
            }

            this.saveBatch(list);

        } catch (IOException e) {
            throw new RuntimeException(ErrorConstant.FILE_READING_FAILED, e);// 文件读取失败，请检查文件是否损坏
        }
    }

    @Override
    public List<BillChatVO> queryBills(BillChatQuery query) {
        Integer userId = BaseContext.getCurrentId();
        List<Bill> bills = null;
        if (query == null) {
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.orderByDesc("date");
            queryWrapper.eq("user_id", userId);
            queryWrapper.last("LIMIT 5");
            bills = this.billMapper.selectList(queryWrapper);
        }else {
            // 查分类id
            Category category = this.categoryService.getByName(query.getCategoryName());
            QueryWrapper<Bill> wrapper = new QueryWrapper<>();
            wrapper.eq(query.getType() != null, "type", query.getType())
                    .eq("user_id", userId)
                    .eq(query.getCategoryName() != null, "category_id", category.getId())
                    .eq(query.getRemark() != null, "remark", query.getRemark())
                    .between(query.getDateStart() != null, "date", query.getDateStart(), query.getDateEnd())
                    .between(query.getAmountStart() != null, "amount", query.getAmountStart(), query.getAmountEnd());
            bills = this.billMapper.selectList(wrapper);
        }
        // 查分类名称
        return bills.stream().map(bill -> {
            BillChatVO billChatVO = new BillChatVO();
            Category category = this.categoryService.getCategoryById(bill.getCategoryId());
            BeanUtils.copyProperties(bill, billChatVO);
            billChatVO.setCategoryName(category.getName());
            return billChatVO;
        }).toList();
    }
}
